package test;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public class JDBCMetaView {

	static String url = "jdbc:mysql://localhost:3306/office";
	static String user = "root";
	static String pass = "root";

	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		Connection conn = null;
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, user, pass);
		getDataBaseMetaInfo(conn);
		getTableInfo(conn, "bs_role");
	}

	/**
	 * 方法说明：获取数据元数据信息
	 */
	public static void getDataBaseMetaInfo(Connection conn) throws Exception {
		DatabaseMetaData dbmd = conn.getMetaData();
		String dataName = dbmd.getDriverName();
		String dataURL = dbmd.getURL();
		System.out.println("**************** DATA META Comment  *********************");
		System.out.println("dataName=" + dataName);
		System.out.println("dataURL=" + dataURL);
		// 获取数据库最大支持字节数
		int dataMaxSize = dbmd.getMaxRowSize();
		System.out.println("dataMaxSize=" + dataMaxSize);
		// 获取数据库表信息
		String[] types = new String[2];
		types[0] = "TABLE";
		types[1] = "VIEW";
		ResultSet results = dbmd.getTables(null, null, "%", types);
		System.out.println("********************tables information********************");
		List<String> list = new ArrayList<String>();
		while (results.next()) {
			System.err.println("----------------------------");
			System.err.println("TABLE_CAT   : " + results.getObject(1));
			System.err.println("TABLE_SCHEM : " + results.getObject(2));
			System.err.println("TABLE_NAME  : " + results.getObject(3));
			System.err.println("TABLE_TYPE  : " + results.getObject(4));
			System.err.println("REMARKS     : " + results.getObject(5));
			list.add(results.getObject(3).toString());
		}
		// 获取表主键信息
		System.out.println("********************PK information********************");
		for (String table : list) {
			System.out.println("********************" + table + "********************");
			ResultSet pkRSet = dbmd.getPrimaryKeys(null, null, table);
			while (pkRSet.next()) {
				System.err.println("TABLE_CAT : " + pkRSet.getObject(1));
				System.err.println("TABLE_SCHEM: " + pkRSet.getObject(2));
				System.err.println("TABLE_NAME : " + pkRSet.getObject(3));
				System.err.println("COLUMN_NAME: " + pkRSet.getObject(4));
				System.err.println("KEY_SEQ : " + pkRSet.getObject(5));
				System.err.println("PK_NAME : " + pkRSet.getObject(6));
			}
		}
	}

	public static void getTableInfo(Connection conn, String tablename) throws Exception {
		String sql = "select * from " + tablename;
		PreparedStatement stmt;
		try {
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();

			for (int i = 1; i <= data.getColumnCount(); i++) {
				// 获得所有列的数目及实际列数
				int columnCount = data.getColumnCount();
				// 获得指定列的列名
				String columnName = data.getColumnName(i);
				// 获得指定列的列值
				// String columnValue = rs.getString(i);
				// 获得指定列的数据类型
				int columnType = data.getColumnType(i);
				// 获得指定列的数据类型名
				String columnTypeName = data.getColumnTypeName(i);
				// 所在的Catalog名字
				String catalogName = data.getCatalogName(i);
				// 对应数据类型的类
				String columnClassName = data.getColumnClassName(i);
				// 在数据库中类型的最大字符个数
				int columnDisplaySize = data.getColumnDisplaySize(i);
				// 默认的列的标题
				String columnLabel = data.getColumnLabel(i);
				// 获得列的模式
				String schemaName = data.getSchemaName(i);
				// 某列类型的精确度(类型的长度)
				int precision = data.getPrecision(i);
				// 小数点后的位数
				int scale = data.getScale(i);
				// 获取某列对应的表名
				String tableName = data.getTableName(i);
				// 是否自动递增
				boolean isAutoInctement = data.isAutoIncrement(i);
				// 在数据库中是否为货币型
				boolean isCurrency = data.isCurrency(i);
				// 是否为空
				int isNullable = data.isNullable(i);
				// 是否为只读
				boolean isReadOnly = data.isReadOnly(i);
				// 能否出现在where中
				boolean isSearchable = data.isSearchable(i);
				System.out.println(columnCount);
				System.out.println("获得列" + i + "的字段名称:" + columnName);
				// System.out.println("获得列" + i + "的字段值:" + columnValue);
				System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
				System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
				System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
				System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
				System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
				System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
				System.out.println("获得列" + i + "的模式:" + schemaName);
				System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
				System.out.println("获得列" + i + "小数点后的位数:" + scale);
				System.out.println("获得列" + i + "对应的表名:" + tableName);
				System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
				System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
				System.out.println("获得列" + i + "是否为空:" + isNullable);
				System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
				System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
